import pandas as pd
import numpy
import xlwt


def fill_excel(wb, ws, df, path, chengji_bili, dachengdu):
    '''
    :param wb: workbook
    :param ws: worksheet
    :param df:
    :param path:输出路径
    :param chengji_bili:比例列表 [平时成绩比例，考试成绩比例]
    :param dachengdu:达成度列表 [课程1A，课程1B，课程2A，课程2B，课程3A，课程3B，课程4A，课程4B]
    :return:df[['学号', '姓名', '课程编号', '考试科目']+[课程目标总成绩i]]
    '''
    list1 = [x for x in range(17, )]
    number_lesson = int(len(dachengdu) / 2)
    list1 = [x for x in range(17, 17 + 1 + (number_lesson - 1) * 3, 3)]
    df['总成绩'] = df['平时成绩'] * chengji_bili[0] + df['考试成绩'] * chengji_bili[1]
    df['总目标达成度'] = df['总成绩'] / 100
    for i in range(1, number_lesson + 1):
        df['课程目标总成绩' + str(i)] = df['考试成绩课程目标' + str(i)] * chengji_bili[1] / dachengdu[i * 2 - 1] + df[
            '平时成绩课程目标' + str(i)] * chengji_bili[0] / \
                                 dachengdu[i * 2 - 2]
    list_name = []
    for i in range(1, number_lesson + 1):
        list_name.append('平时成绩课程目标' + str(i))
        list_name.append('考试成绩课程目标' + str(i))
        list_name.append('课程目标总成绩' + str(i))
    list_name.append('总目标达成度')

    list2 = ['学号', '姓名', '课程编号', '考试科目', '考试状态', '性别', '是否留学生', '学院', '考试日期', '班级',
             '年级', '备注', '平时成绩', '考试成绩', '总成绩'] + list_name
    df = df.loc[:, list2]
    # wb = openpyxl.load_workbook(path)
    # ws = wb[sheel]
    # for i in range(0, df.shape[0]):
    #     data_list = df.iloc[i, :].tolist()
    #     for j in range(0, len(data_list)):
    #         ws.cell(row=i + 8, column=j + 1).value = data_list[j]
    # wb.save(path)
    for i in range(0, df.shape[0]):
        data_list = df.iloc[i, :].tolist()
        for j in range(0, len(data_list)):
            if j < 12:
                pattern = xlwt.Pattern()
                pattern.pattern = xlwt.Pattern.SOLID_PATTERN
                pattern.pattern_fore_colour = 22
                style = xlwt.XFStyle()
                style.pattern = pattern
                ws.write(i + 7, j, data_list[j], style)
            elif j == 14:
                pattern = xlwt.Pattern()
                pattern.pattern = xlwt.Pattern.SOLID_PATTERN
                style = xlwt.XFStyle()
                style.pattern = pattern
                pattern.pattern_fore_colour = 3
                ws.write(i + 7, j, data_list[j], style)
            elif j in list1:
                pattern1 = xlwt.Pattern()
                pattern1.pattern = xlwt.Pattern.SOLID_PATTERN
                style1 = xlwt.XFStyle()
                style1.pattern = pattern1
                pattern1.pattern_fore_colour = 4
                ws.write(i + 7, j, data_list[j], style1)
            else:
                ws.write(i + 7, j, data_list[j])
    wb.save(path)

    output = df[['学号', '姓名', '课程编号', '考试科目']]
    for x in range(1, number_lesson + 1):
        output = pd.concat((output, df['课程目标总成绩' + str(x)]), axis=1)
    output = numpy.array(output)
    output = output.tolist()
    return output
